/**
 * @Copyright: Interactive Business Solution Pte Ltd, Singapore
 * @Author: Liu Xiaoxiong, 2008-03-31
 * @Version: 080401.002
 * @Change: Li Wen, 2008-04-01
 * Data access object of user object
 * 
 * TODO: other dao functions, such as insert, update, delete functions
 */
package com.ibsol.database
{
	import com.ibsol.model.User;
	
	import flash.data.SQLConnection;
	import flash.data.SQLStatement;
	import flash.events.SQLErrorEvent;
	import flash.events.SQLEvent;
	
	import mx.collections.ArrayCollection;
	
	public class UserDAO
	{
		private var sqlCon:SQLConnection;
		public function UserDAO(){
			sqlCon = DBConnManager.getConnection("tourix");
		}
		/**
		 * Load current user from database 
		 */
		public function getSystemUser(resultHandler:Function,faultHandler:Function=null):void{
			var stmt:SQLStatement=new SQLStatement();
			stmt.sqlConnection=sqlCon;
			stmt.text="select u.* from user as u inner join system as s on u.id=s.uid";
			stmt.addEventListener(SQLEvent.RESULT,function (e:SQLEvent):void{
				var ac:ArrayCollection=toArrayCollection(stmt.getResult().data);
				if(ac!=null&&ac.length!=0){
					resultHandler.call(this,ac.getItemAt(0) as User);
				}
			});
			stmt.addEventListener(SQLErrorEvent.ERROR,faultHandler==null?onfaultHandler:faultHandler);
			stmt.execute();
		}
		/**
		 * Load all users from database, only certain level users can use this function
		 */
		public function loadUsers(resultHandler:Function, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = sqlCon;
			sqlStr.text = "SELECT * FROM user WHERE offlineOperator != 'DEL'";
			sqlStr.addEventListener(SQLEvent.RESULT, function(e:SQLEvent):void
			{
				var ac:ArrayCollection = toArrayCollection(sqlStr.getResult().data);
				resultHandler.call(this, ac);
			});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? onfaultHandler : faultHandler);
			sqlStr.execute();
		}
		
		public function getUserListBySystemId(resultHandler:Function, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = sqlCon;
			sqlStr.text = "SELECT user.* FROM user, system as s WHERE user.id = s.uid";
			sqlStr.addEventListener(SQLEvent.RESULT, function(e:SQLEvent):void
			{
				var ac:ArrayCollection = toArrayCollection(sqlStr.getResult().data);
				resultHandler.call(this, ac);
			});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? onfaultHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Creates a new user into the database
		 * User(id, acc, pwd, level, addr, mobile, tel, fax, email, remark, pairid, offlineOperator, updateTime)
		 * @param:		user:User				user object which will be inserted
		 * 				resultHandler:Function	result handler adapter
		 * 				faultHandler:Function	fault handler adapter
		 * @return:		void
		 */
		public function insertUser(user:User, resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = sqlCon;
			sqlStr.text = "INSERT INTO user (acc, pwd, level, addr, mobile, tel, fax, email, remark, pairid, offlineOperator, updateTime) " + 
					" VALUES ('" + user.acc + "','" + user.pwd + "'," + user.level + ",'" + user.addr + "','" + user.mobile + "','" + 
					user.tel + "','" + user.fax + "','" + user.email + "','" + user.remark + "','" + user.pairid + "','NEW','T')";
			sqlStr.addEventListener(SQLEvent.RESULT, function(event:SQLEvent):void
			{
				user.id = sqlStr.getResult().lastInsertRowID;
				if(resultHandler != null)
					resultHandler.call(this, user.id);
			});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? onfaultHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Updates a user record in database
		 * @param:	user:User				user object which will be updated
		 * 			resultHandler:Function	
		 */
		public function updateUser(user:User, resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = sqlCon;
			sqlStr.text = "UPDATE user SET acc = '" + user.acc + "', pwd='" + user.pwd + "', level = " + user.level + ", addr = '" + 
						  user.addr + "', mobile = '" + user.mobile + "', tel = '" + user.tel + "', fax = '" + user.fax + "', email = '" +
						  user.email + "', remark = '" + user.remark + "', pairid = '" + user.pairid + "', offlineOperator = 'EDIT', " + 
						  " updateTime = 'T' WHERE id = " + user.id;
			sqlStr.addEventListener(SQLEvent.RESULT, function(event:SQLEvent):void
			{
				if(resultHandler != null)
					resultHandler.call(this);
			});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? onfaultHandler : faultHandler);
			sqlStr.execute();
		}
		
		/**
		 * Check the user validation
		 */
		public function checkUserValidate(user:User, resultHandler:Function, faultHandler:Function = null):void
		{
			var stmt:SQLStatement = new SQLStatement();
			stmt.sqlConnection = sqlCon;
			stmt.text = "select * from user where acc = '" + user.acc + "' and pwd = '" + user.pwd + "'";
			stmt.addEventListener(SQLEvent.RESULT, function (e:SQLEvent):void{
				var ac:ArrayCollection = toArrayCollection(stmt.getResult().data);
				if(ac != null && ac.length != 0)
				{
					resultHandler.call(this, ac.getItemAt(0).id);
				}
				else
					resultHandler.call(this, 0);
			});
			stmt.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? onfaultHandler:faultHandler);
			stmt.execute();
		}
		
		private function toArrayCollection(array:Array):ArrayCollection{
			if(array==null)
				return new ArrayCollection();
			var ac:ArrayCollection=new ArrayCollection();
			for(var i:int=0;i<array.length;i++){
				ac.addItem(toUser(array[i]));
			}
			return ac;
		}
		
		private function toUser(o:Object):User{
			var u:User=new User();
			u.id=o.id;
			u.acc=o.acc;
			u.level=o.level;
			u.offlineOperator=o.offlineOperator;
			u.pairid=o.pairid;
			u.photo=o.photo;
			u.pwd=o.pwd;
			u.updateTime=o.updateTime;
			u.email=o.email;
			u.mobile=o.mobile;
			u.tel=o.tel;
			u.addr = o.addr;
			u.fax = o.fax;
			u.remark = o.remark;
			return u;
		}
		private function onfaultHandler(e:SQLErrorEvent):void{
			trace(e.text);
		}
	}
}